package kines.ct;

import kines.SerializableDB;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.text.NumberFormat;

/**
 * <p>Title: CT</p>
 * <p>Description: Intranet Consorcio Terrenos</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: Consorcio Terrenos</p>
 * @author Jose Delgado
 * @version 1.0
 */

public class Development implements SerializableDB {
  /**
   * Tipos para reporte de cartera vencida
   */
  public static int CLIENTES = 1;
  public static int INTERCAMBIOS = 2;
  /**
   * Formato de fecha
   */
  static SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
  static NumberFormat nf = NumberFormat.getInstance();
  static{
    nf.setMinimumFractionDigits(2);
    nf.setMaximumFractionDigits(2);
  }
  /**
   * Minimum Deposit
   */
  private double minimumDeposit;
  /**
   * String Identifier
   */
  /**
   * Development Name
   */
  private String name;
  /**
   * String Identifier
   */
  private String code;
  /**
   * Owner Address
   */
  private String ownerAddress;
  /**
   * Owner Name
   */
  private String ownerName;
  /**
   * Owner Colony
   */
  private String ownerColony;
  /**
   * Owner City
   */
  private String ownerCity;
  /**
   * Owner State
   */
  private String ownerState;
  /**
   * Developer Location
   */
  private String location ;
  /**
   * Gets development code
   * @param development Development Name
   * @return development code
   * @throws java.lang.Exception
   */
  public static String getCode( String development )throws Exception{
    String codeR = "";
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    Statement stmt_development = ConnRs.createStatement( );
    String str_development = "select iddesarrollo as cvedesarrollo from desarrollos  nombredesarrollo = '"+(development!=null?development.trim():"")+"'";
    ResultSet rs_development = stmt_development.executeQuery( str_development );
    if( rs_development.next() ){
      codeR = rs_development.getString("cvedesarrollo");
    }else{
      codeR = "";
    }
    ConnRs.close();
    return codeR;
  }
  /**
   * Gets name field
   * @return name field
   */
  public String getName( ){
    return name;
  }
  /**
   * Sets location field
   * @param location
   */
  public void setName( String name ){
    this.name = name;
  }
  /**
   * Gets location field
   * @return location field
   */
  public String getLocation( ){
    return location;
  }
  /**
   * Sets location field
   * @param location
   */
  public void setLocation( String location ){
    this.location = location;
  }
  /**
   * Gets Owner name field
   * @return Owner name field
   */
  public String getOwnerName( ){
    return ownerName;
  }
  /**
   * Sets Owner Name field
   * @param Owner name
   */
  public void setOwnerName( String ownerName ){
    this.ownerName = ownerName;
  }
  /**
   * Gets Owner Address field
   * @return Owner Address field
   */
  public String getOwnerAddress( ){
    return ownerAddress;
  }
  /**
   * Sets Owner Address field
   * @param Owner Address
   */
  public void setOwnerAddress( String ownerAddress ){
    this.ownerAddress = ownerAddress;
  }
  /**
   * Gets Owner Colony field
   * @return Owner Colony field
   */
  public String getOwnerColony( ){
    return ownerColony;
  }
  /**
   * Sets Owner Colony field
   * @param Owner Colony
   */
  public void setOwnerColony( String ownerColony ){
    this.ownerColony = ownerColony;
  }
  /**
   * Gets Owner City field
   * @return Owner City field
   */
  public String getOwnerCity( ){
    return ownerCity;
  }
  /**
   * Sets Owner City field
   * @param Owner City
   */
  public void setOwnerCity( String ownerCity ){
    this.ownerCity = ownerCity;
  }
  /**
   * Gets Owner State field
   * @return Owner State field
   */
  public String getOwnerState( ){
    return ownerState;
  }
  /**
   * Sets Owner State field
   * @param Owner State
   */
  public void setOwnerState( String ownerState ){
    this.ownerState = ownerState;
  }
  /**
  * Gets Minimum Deposit field
  * @return Minimum Deposit field
  */
 public double getMinimumDeposit( ){
   return minimumDeposit;
 }
 /**
  * Sets Minimum Deposit field
  * @param Minimum Deposit
  */
 public void setMinimumDeposit( double minimumDeposit ){
   this.minimumDeposit = minimumDeposit;
 }
  /**
   * Sets code field
   * @param code field
   */
  protected void setCode( String code ){
    this.code = code;
  }
  /**
   * Gets code field
   * @return code field
   */
  public String getCode( ){
    return code;
  }
  /**
   * Default Contructor
   */
  private Development( ){

  }

  /**
   * Contructor with id
   * @param Development's id
   */
  private Development( String cveDesarrollo )throws Exception{
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    Statement stmt_develop = ConnRs.createStatement( );
    String str_develop =
        " select ubicacion, " +
        "        iddesarrollo,  " +
        "        nombredesarrollo as nombre, " +
        "        MontoMinimoApartado, " +
        "        dueno as nombredu,  " +
        "        direccion,  " +
        "        col,   " +
        "        ciudad,  " +
        "        estado  " +
        " from 	desarrollos d  " +
        "                left join duenos du  " +
        "                on d.dueno = du.iddueno " +
        " where 	idDesarrollo='" + cveDesarrollo + "' ";
    ResultSet rs_develop = stmt_develop.executeQuery( str_develop );
    if( rs_develop.next() ){
      name = rs_develop.getString("nombre");
      code = cveDesarrollo;
      minimumDeposit = rs_develop.getDouble("MontoMinimoApartado");
      this.ownerAddress = rs_develop.getString("direccion");
      this.ownerCity = rs_develop.getString("ciudad");
      this.ownerColony = rs_develop.getString("col");
      this.ownerName = rs_develop.getString("nombredu");
      this.ownerState = rs_develop.getString("estado");
      this.location = rs_develop.getString("ubicacion");
    }
    ConnRs.close();
  }
  public static Object[] getData()throws Exception{
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    ArrayList developments = new ArrayList();
    Statement stmt_development = ConnRs.createStatement( );
    String str_development =
        " select iddesarrollo as cvedesarrollo, "+
        "        ubicacion, " +
        "        iddesarrollo,  " +
        "        nombredesarrollo as nombre, " +
        "        MontoMinimoApartado, " +
        "        dueno as nombredu,  " +
        "        direccion,  " +
        "        col,   " +
        "        ciudad,  " +
        "        estado  " +
        " from 	 desarrollos d  " +
        "                left join duenos du  " +
        "                on d.dueno = du.iddueno " +
        " order by nombredesarrollo ";
    ResultSet rs_development = stmt_development.executeQuery( str_development );
    while( rs_development.next() ){
      Development dev = new Development( );
      dev.setCode( rs_development.getString("cvedesarrollo") );
      dev.setName( rs_development.getString("nombre") );
      dev.setMinimumDeposit( rs_development.getDouble( "MontoMinimoApartado" ) );
      dev.setOwnerAddress( rs_development.getString("direccion") );
      dev.setOwnerColony( rs_development.getString("col") );
      dev.setOwnerName( rs_development.getString("nombredu") );
      dev.setOwnerCity( rs_development.getString("ciudad") );
      dev.setOwnerState( rs_development.getString("estado") );
      dev.setLocation(rs_development.getString("ubicacion"));
      developments.add(dev);
    }
    ConnRs.close();
    return developments.toArray();
  }
  public static Object[] getData(String where, String order)throws Exception{
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    ArrayList developments = new ArrayList();
    Statement stmt_development = ConnRs.createStatement( );
    String str_development =
        " select iddesarrollo as cvedesarrollo, "+
        "        ubicacion, " +
        "        iddesarrollo,  " +
        "        nombredesarrollo as nombre, " +
        "        MontoMinimoApartado, " +
        "        dueno as nombredu,  " +
        "        direccion,  " +
        "        col,   " +
        "        ciudad,  " +
        "        estado  " +
        " from 	 desarrollos d  " +
        "                left join duenos du  " +
        "                on d.dueno = du.iddueno ";
    str_development += (where !=null && !where.equals("")?" where "+where:"");
    str_development += (order !=null && !order.equals("")?" order by "+order:"");
    ResultSet rs_development = stmt_development.executeQuery( str_development );
    while( rs_development.next() ){
      Development dev = new Development( );
      dev.setCode( rs_development.getString("cvedesarrollo") );
      dev.setName( rs_development.getString("nombre") );
      dev.setMinimumDeposit( rs_development.getDouble( "MontoMinimoApartado" ) );
      dev.setOwnerAddress( rs_development.getString("direccion") );
      dev.setOwnerColony( rs_development.getString("col") );
      dev.setOwnerName( rs_development.getString("nombredu") );
      dev.setOwnerCity( rs_development.getString("ciudad") );
      dev.setOwnerState( rs_development.getString("estado") );
      dev.setLocation(rs_development.getString("ubicacion"));
      developments.add(dev);
    }
    ConnRs.close();
    return developments.toArray();
  }
  public static Object[] getReporteCarteraSensibilizada( Calendar fechaCierre, int int_diasAtraso )throws Exception{
	  return getReporteCarteraSensibilizada(fechaCierre, int_diasAtraso, null);
  }
  public static Object[] getReporteCarteraSensibilizada( Calendar fechaCierre, int int_diasAtraso, String user )throws Exception{
    ArrayList arrL_retVal = new ArrayList();
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
    String str_carteraVencidaS;
    if( int_diasAtraso == 60 ){
      str_carteraVencidaS = "call sp_carteraVencidaSesibilizada60('"+sdf.format( fechaCierre.getTime() )+"', '"+user+"')";
    }else{
      str_carteraVencidaS = "call sp_carteraVencidaSesibilizada90('"+sdf.format( fechaCierre.getTime() )+"', '"+user+"')";
    }
    Statement stmt_carteraVencidaS = ConnRs.createStatement();
    ResultSet rs_carteraVencidaS = stmt_carteraVencidaS.executeQuery(str_carteraVencidaS);
    String str_cveDesarrollo;
    int int_tipoDesarrollo;
    while( rs_carteraVencidaS.next() ){
      str_cveDesarrollo = rs_carteraVencidaS.getString("iddesarrollo");
      int_tipoDesarrollo = rs_carteraVencidaS.getInt("tipodesarrollo");
      Object [] objs_campos = {
          str_cveDesarrollo,
          new Integer( int_tipoDesarrollo ),
          new Double( rs_carteraVencidaS.getDouble("importe_monto_vencido") ),
          new Double( rs_carteraVencidaS.getDouble("importe_tipo1") ),
          new Integer( rs_carteraVencidaS.getInt("clientes_tipo1") ),
          new Double( rs_carteraVencidaS.getDouble("importe_tipo2") ),
          new Integer( rs_carteraVencidaS.getInt("clientes_tipo2") ),
          new Double( rs_carteraVencidaS.getDouble("importe_tipo3") ),
          new Integer( rs_carteraVencidaS.getInt("clientes_tipo3") ),
          new Double( rs_carteraVencidaS.getDouble("importe_tipo4") ),
          ( int_diasAtraso == 60 ? null : new Integer( rs_carteraVencidaS.getInt("clientes_tipo4") ) ),
          ( int_diasAtraso == 60 ? null : new Double( rs_carteraVencidaS.getDouble("importe_tipo5") ) ),
          new Double( rs_carteraVencidaS.getDouble("total_porvencer") ),
          new Double( rs_carteraVencidaS.getDouble("suma") ),
          new Double( rs_carteraVencidaS.getDouble("cartera_documentada") ),
          new Double( rs_carteraVencidaS.getDouble("carteraxcobrar_vigente") ),
          new Double( rs_carteraVencidaS.getDouble("porcentaje_cartera_vencida") ),
          new Double( rs_carteraVencidaS.getDouble("porcentaje_cartera_vencida_acumulado") ),
          new Double( rs_carteraVencidaS.getDouble("porcentaje_cartera_total_documentada") ),
          new Double( rs_carteraVencidaS.getDouble("porcentaje_creditos_vencidos") ),
          new Double( rs_carteraVencidaS.getDouble("cartera_documentadaI") ),
          new Double( rs_carteraVencidaS.getDouble("cartera_documentadaT") )
      };
      arrL_retVal.add(objs_campos);
    }
    rs_carteraVencidaS.close();
    stmt_carteraVencidaS.close();
    ConnRs.close();
    return arrL_retVal.toArray();
  }
  /**
   * Obtiene el reporte de cartera vencida ( totales )
   * @param fechaCierre Calendar
   * @throws Exception
   * @return double[][]
   */
  public Object[] getReporteCartera( Calendar fechaCierre, int int_tipo )throws Exception{
	  return getReporteCartera(fechaCierre, int_tipo, null);
  }
  public Object[] getReporteCartera( Calendar fechaCierre, int int_tipo, String dueno )throws Exception{
    ArrayList arrL_retVal = new ArrayList();
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    String str_carteraVencida = " call sp_carteraVencidaDetalle ('"+sdf.format(fechaCierre.getTime())+"', '"+code+"',  "+int_tipo+", '"+dueno+"') ";
    //System.out.println(str_carteraVencida);
     if( str_carteraVencida.equals("") ){
       throw new SQLException(str_carteraVencida);
     }
    Statement stmt_carteraVencida = ConnRs.createStatement();
    ResultSet rs_carteraVencida = stmt_carteraVencida.executeQuery(str_carteraVencida);
    String str_idConvenio = "";
    String str_idConvenioAnt = "";
    String str_descConvenio = "";
    int    int_idAtraso = -1;
    double dbl_interes = 0;
    double dbl_importe = 0;
    double dbl_total = 0;
    double dbl_interesTot = 0;
    double dbl_importeTot = 0;
    double dbl_totalTot = 0;
    int int_i = 0;
    Object [] objs_campos = new Object[20];

    while( rs_carteraVencida.next() ){
      str_idConvenio = rs_carteraVencida.getString("idConvenio");
      str_descConvenio = rs_carteraVencida.getString("descConvenio");
      int_idAtraso = rs_carteraVencida.getInt("idAtraso");
      dbl_interes = rs_carteraVencida.getDouble("interes");
      dbl_importe = rs_carteraVencida.getDouble("importe");
      dbl_total = rs_carteraVencida.getDouble("total");

      if( !str_idConvenioAnt.equals(str_idConvenio) ){
        if( int_i > 0 ){
          objs_campos[1] = new Double(dbl_importeTot);
          objs_campos[2] = new Double(dbl_interesTot);
          objs_campos[3] = new Double(dbl_totalTot);
          arrL_retVal.add(objs_campos);

          objs_campos = new Object[20];

          dbl_interesTot = 0;
          dbl_importeTot = 0;
          dbl_totalTot = 0;

        }
        objs_campos[0] = str_descConvenio;
        objs_campos[19] = str_idConvenio;
        int_i++;
      }

      if( int_idAtraso != 5 ){
        dbl_interesTot += dbl_interes;
        dbl_importeTot += dbl_importe;
        dbl_totalTot += dbl_total;
      }

      objs_campos[( ( int_idAtraso-1 )*3 )+4] = new Double( dbl_importe );
      objs_campos[( ( int_idAtraso-1 )*3 )+5] = new Double( dbl_interes );
      objs_campos[( ( int_idAtraso-1 )*3 )+6] = new Double( dbl_total );

      str_idConvenioAnt = str_idConvenio;
    }
    objs_campos[1] = new Double( dbl_importeTot );
    objs_campos[2] = new Double( dbl_interesTot );
    objs_campos[3] = new Double( dbl_totalTot );
    arrL_retVal.add(objs_campos);

    rs_carteraVencida.close();
    stmt_carteraVencida.close();
    ConnRs.close();
    return arrL_retVal.toArray();
  }
  /**
   * Obtiene el reporte de cartera vencida ( totales )
   * @param fechaCierre Calendar
   * @throws Exception
   * @return double[][]
   */
  public static Object[] getReporteCarteraTodosNueva( Calendar fechaCierre, int int_tipo )throws Exception{
    SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
    ArrayList arrL_retVal = new ArrayList();
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    String str_carteraVencida =
        " exec sp_carteraVencidaNueva '"+sdf.format(fechaCierre.getTime())+"', "+int_tipo+" ";
    Statement stmt_carteraVencida = ConnRs.createStatement();
    ResultSet rs_carteraVencida = stmt_carteraVencida.executeQuery(str_carteraVencida);
    String str_cveDesarrollo = "";
    String str_cveDesarrolloAnt = "";
    double dbl_importe = 0;
    double dbl_importeTot = 0;
    int int_idAtraso = 0;
    int int_i = 0;
    Object [] objs_campos = new Object[10];

    while( rs_carteraVencida.next() ){
      str_cveDesarrollo = rs_carteraVencida.getString("cvedesarrollo");
      dbl_importe = rs_carteraVencida.getDouble("importe");
      int_idAtraso = rs_carteraVencida.getInt("idAtraso");

      if( !str_cveDesarrolloAnt.equals(str_cveDesarrollo) ){
        if( int_i > 0 ){
          objs_campos[1] = new Integer( (int)Math.round( dbl_importeTot/1000 ) );
          arrL_retVal.add(objs_campos);

          objs_campos = new Object[10];

          dbl_importeTot = 0;

        }
        objs_campos[0] = str_cveDesarrollo;
        int_i++;
      }
      if( int_idAtraso != 5 ){
        dbl_importeTot += dbl_importe;
      }
      objs_campos[( ( int_idAtraso-1 ) )+2] = new Integer( (int)Math.round( dbl_importe/1000 ) );

      str_cveDesarrolloAnt = str_cveDesarrollo;
    }
    objs_campos[1] = new Integer( (int)Math.round( dbl_importeTot/1000 ) );
    arrL_retVal.add(objs_campos);

    rs_carteraVencida.close();
    stmt_carteraVencida.close();
    ConnRs.close();
    return arrL_retVal.toArray();
  }
  /**
   * Gets String with options to html combo
   * @throws Exception
   * @return String
   */
  public static String getHtmlCombo( String selected )throws Exception{
    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn,username,password);
    String developmentHtmlCombo = "";
    Statement stmt_dev = ConnRs.createStatement( );
    String str_dev = "select idDesarrollo, nombreDesarrollo, staventaDesarrollo from desarrollos order by staventaDesarrollo desc";
    ResultSet rs_dev = stmt_dev.executeQuery( str_dev );
    String id = "";
    int    status  = -1;
    while( rs_dev.next() ){
      id = rs_dev.getString("idDesarrollo");
      status  = rs_dev.getInt("staventaDesarrollo");
      developmentHtmlCombo += "<option "+(id.equals((selected!=null?selected:""))?"selected":"")+" value = '"+id+"'>"+(status==1?"EN  VENTA":"VENDIDOS")+" - "+rs_dev.getString("nombreDesarrollo")+"</option>\n";
    }
    ConnRs.close();
    return developmentHtmlCombo;
  }
  public static Development getData( String codeDevelopment )throws Exception{
    return new Development( codeDevelopment );
  }
  /**
   * Gets Status Report
   * @throws Exception
   * @return Object[]
   */
  public Object[] getReportStatus( Calendar ini, Calendar end )throws Exception{
	  return getReportStatus(ini, end, null);
  }
  public Object[] getReportStatus( Calendar ini, Calendar end, String dueno )throws Exception{
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( "yyyy-MM-dd" );

    Object[]  status = {new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 ), new Integer( 0 ), new Double( 0 )};

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String     sql_csreport  =
        " SELECT   StaVenta, "+
                 " case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end intercambio, "+
                 " count(lotes.idLote) as LOTESXCOTINTER, "+
                 " SUM(Superficie) AS SUPERFICIE "+
        " FROM     lotes "+
                 " LEFT JOIN ventas ON ventas.idlote = lotes.idlote AND ventas.estadoactual = 'A' "+
                 " LEFT JOIN apartados ON ventas.idventa = apartados.idventa AND apartados.StaApartado = 'A' "+
        " WHERE    idDesarrollo='"+code+"' ";
    if( dueno != null && !dueno.equals("") ){
    	sql_csreport += " and iddueno = '"+dueno+"' ";
    }
    if( end != null ){
      if( ini == null ){
        sql_csreport  += " and ( ( fechafirma < '"+sdf.format(end.getTime())+"' and StaVenta in ( 'C', 'V' ) ) or (fechaApartado < '"+sdf.format(end.getTime())+"' and StaVenta = 'A') or StaVenta = 'L' ) ";
      }else{
        sql_csreport  += " and ( ( fechafirma between '"+sdf.format(ini.getTime())+"' and '"+sdf.format(end.getTime())+"' and StaVenta in ('C','V') ) or ( fechaApartado between '"+sdf.format(ini.getTime())+"' and '"+sdf.format(end.getTime())+"' and StaVenta = 'A' ) or StaVenta = 'L') ";
      }
    }
    sql_csreport  +=
        " group by StaVenta, case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end"+
        " order by 1,2 ";
    Statement  stmt_csreport = ConnRs.createStatement();
    //System.out.println(sql_csreport);
    ResultSet  rs_csreport   = stmt_csreport.executeQuery( sql_csreport );

    char   statusTmp = ' ';
    char   intercTmp = ' ';
    int    totalTmp  = 0;
    double superTmp = 0;

    while( rs_csreport.next() ){
      statusTmp = rs_csreport.getString("staventa").charAt(0);
      intercTmp = rs_csreport.getString("intercambio").charAt(0);
      totalTmp  = rs_csreport.getInt("LOTESXCOTINTER");
      superTmp  = rs_csreport.getDouble("SUPERFICIE");

      if( statusTmp == Terrain.AVAILABLE ){
        status[(intercTmp == Terrain.EXCHANGE?2:0)] = new Integer( totalTmp );
        status[(intercTmp == Terrain.EXCHANGE?3:1)] = new Double( superTmp );
      }
      if( statusTmp == Terrain.APART ){
        status[(intercTmp == Terrain.EXCHANGE?6:( intercTmp == Terrain.ESPECIAL ? 16 : 4 ))] = new Integer( totalTmp );
        status[(intercTmp == Terrain.EXCHANGE?7:( intercTmp == Terrain.ESPECIAL ? 17 : 5 ))] = new Double( superTmp );
      }
      if( statusTmp == Terrain.SOLD ){
        status[(intercTmp == Terrain.EXCHANGE?10:8)] = new Integer( totalTmp );
        status[(intercTmp == Terrain.EXCHANGE?11:9)] = new Double( superTmp );
      }
      if( statusTmp == Terrain.CONTRACT ){
        status[(intercTmp == Terrain.EXCHANGE?14:12)] = new Integer( totalTmp );
        status[(intercTmp == Terrain.EXCHANGE?15:13)] = new Double( superTmp );
      }
    }

    ConnRs.close();
    return status;
  }
  /**
   * Gets Reporte by Coto and Status
   * @return Object[]
   * @exception Throws exception if there is a error in database
   */
  public Object[] getReportCotoStatus( )throws Exception{
	  return getReportCotoStatus(null);
  }
  public Object[] getReportCotoStatus( String dueno )throws Exception{
    ArrayList cotos  = new ArrayList( );
    int[]     status = new int[11];

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String     sql_csreport  =
        "  select  coto, "+
        "          upper(staventa) as staventa,  " +
        "          case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end as intercambio,  " +
        "          count(*) as total,  " +
        "          sum( superficie ) as superficie  " +
        " from 	   lotes  " +
        "                left join ventas v  " +
        "                on lotes.idlote = v.idlote and  " +
        "                   v.estadoactual = 'A' " +
        " where    iddesarrollo = '" + code + "' "+( ( dueno!=null && !dueno.equals("") )?" and iddueno = '"+dueno+"' ":"")+" " +
        " group by coto, staventa,case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end " +
        " order by 1,2,3,4 ";

    Statement  stmt_csreport = ConnRs.createStatement();
    ResultSet  rs_csreport   = stmt_csreport.executeQuery( sql_csreport );

    int  cotoTmp   = -1;
    int  cotAntTmp = -1;
    char statusTmp = ' ';
    char intercTmp = ' ';
    int  totalTmp  = 0;
    int  i         = 0;

    while( rs_csreport.next() ){
      cotoTmp   = rs_csreport.getInt("coto");
      statusTmp = rs_csreport.getString("staventa").charAt(0);
      intercTmp = rs_csreport.getString("intercambio").charAt(0);
      totalTmp  = rs_csreport.getInt("total");

      if( cotoTmp != cotAntTmp && i > 0 ){
        status[9] = (status[1]+status[2]+status[3]+status[4]+status[5]+status[6]+status[7]+status[8]+status[10]);
        cotos.add(status);
        status = new int[11];
      }

      status[0] = cotoTmp;
      if( statusTmp == Terrain.AVAILABLE ){
        status[(intercTmp == Terrain.EXCHANGE?2:1)] = totalTmp;
      }
      if( statusTmp == Terrain.APART ){
        status[(intercTmp == Terrain.EXCHANGE?4:(intercTmp == Terrain.ESPECIAL ? 10 : 3 ) )] = totalTmp;
      }
      if( statusTmp == Terrain.SOLD ){
        status[(intercTmp == Terrain.EXCHANGE?6:5)] = totalTmp;
      }
      if( statusTmp == Terrain.CONTRACT ){
        status[(intercTmp == Terrain.EXCHANGE?8:7)] = totalTmp;
      }

      i++;
      cotAntTmp = cotoTmp;
    }
    status[9] = (status[1]+status[2]+status[3]+status[4]+status[5]+status[6]+status[7]+status[8]+status[10]);
    cotos.add(status);

    ConnRs.close();
    return cotos.toArray();
  }
  /**
   * Gets Reporte by Coto, Block and Status
   * @throws Exception
   * @return Object[]
   */
  public Object[] getReportBlockStatus( )throws Exception{
	return getReportBlockStatus(null);
  }
  
  public Object[] getReportBlockStatus( String dueno )throws Exception{
    ArrayList cotos  = new ArrayList( );
    Object[]  status = {new Integer(0),new Character(' '),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Integer(0),new Double(0),new Integer(0)};

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String     sql_csreport  =
        " select  coto, "+
        "          manzana,  " +
        "          upper(staventa) as staventa,  " +
        "          case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end as intercambio,  " +
        "          count(*) as total,  " +
        "          sum( superficie ) as superficie  " +
        " from 	   lotes  " +
        "                left join ventas v  " +
        "                on lotes.idlote = v.idlote and  " +
        "                   v.estadoactual = 'A' " +
        " where    iddesarrollo = '" + code + "' "+( (dueno!=null&&!dueno.equals(""))?" and iddueno = '"+dueno+"' ":"")+" " +
        " group by coto, manzana, staventa, case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end " +
        " order by 1,2,3,4 ";
    Statement  stmt_csreport = ConnRs.createStatement();
    ResultSet  rs_csreport   = stmt_csreport.executeQuery( sql_csreport );

    int    cotoTmp     = -1;
    int    cotAntTmp   = -1;
    char   manzTmp     = ' ';
    char   manAntTmp   = ' ';
    char   statusTmp   = ' ';
    char   intercTmp   = ' ';
    int    totalTmp    = 0;
    double superTmp    = 0;
    double superTotTmp = 0;
    int    i           = 0;

    while( rs_csreport.next() ){
      cotoTmp   = rs_csreport.getInt("coto");
      manzTmp   = rs_csreport.getString("manzana").charAt(0);
      statusTmp = rs_csreport.getString("staventa").charAt(0);
      intercTmp = rs_csreport.getString("intercambio").charAt(0);
      superTmp  = rs_csreport.getDouble("superficie");
      totalTmp  = rs_csreport.getInt("total");

      if( ( cotAntTmp != cotoTmp || manAntTmp != manzTmp ) && i > 0 ){
        status[10] = new Integer(((Integer)status[2]).intValue()+((Integer)status[3]).intValue()+((Integer)status[4]).intValue()+((Integer)status[5]).intValue()+((Integer)status[6]).intValue()+((Integer)status[7]).intValue()+((Integer)status[8]).intValue()+((Integer)status[9]).intValue()+((Integer)status[12]).intValue());
        status[11] = new Double( superTotTmp );
        cotos.add(status);

        superTotTmp = 0;
        status      = new Object[13];

        status[2] = new Integer( 0 );
        status[3] = new Integer( 0 );
        status[4] = new Integer( 0 );
        status[5] = new Integer( 0 );
        status[6] = new Integer( 0 );
        status[7] = new Integer( 0 );
        status[8] = new Integer( 0 );
        status[9] = new Integer( 0 );
        status[12] = new Integer( 0 );
      }

      status[0]  = new Integer( cotoTmp );
      status[1]  = new Character( manzTmp );
      superTotTmp += superTmp;
      if( statusTmp == Terrain.AVAILABLE ){
        status[(intercTmp == Terrain.EXCHANGE?3:2)] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.APART ){
        status[(intercTmp == Terrain.EXCHANGE?5: ( intercTmp == Terrain.ESPECIAL ? 12 : 4 ) )] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.SOLD ){
        status[(intercTmp == Terrain.EXCHANGE?7:6)] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.CONTRACT ){
        status[(intercTmp == Terrain.EXCHANGE?9:8)] = new Integer( totalTmp );
      }

      i++;
      manAntTmp = manzTmp;
      cotAntTmp = cotoTmp;
    }
    status[10] = new Integer(((Integer)status[2]).intValue()+((Integer)status[3]).intValue()+((Integer)status[4]).intValue()+((Integer)status[5]).intValue()+((Integer)status[6]).intValue()+((Integer)status[7]).intValue()+((Integer)status[8]).intValue()+((Integer)status[9]).intValue()+((Integer)status[12]).intValue());
    status[11] = new Double( superTotTmp );
    cotos.add(status);

    ConnRs.close();
    return cotos.toArray();
  }
  /**
   * Para reporte concentrado por status
   * @return Object[] Listado de desarrollos y sus status
   * @throws SQLException Si ocurre algun error en la base de datos
   */
  public static Object[] getReportConcentStatus( )throws Exception{
    ArrayList desarrollos  = new ArrayList( );
    Object[]     status = {
        new Integer(0), new Integer(0), new Integer(0),
        new Integer(0), new Integer(0), new Integer(0),
        new Integer(0), new Integer(0), new Integer(0),
        new Integer(0), new Integer(0)
    };

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String     sql_csreport  =
        " select   d.iddesarrollo, "+
        "          upper(t.staventa) as staventa, " +
        "          case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end as intercambio, " +
        "          count( * ) as total " +
        " from	   desarrollos d "+
        "                inner join lotes t " +
        "                on d.iddesarrollo = t.iddesarrollo "+
        "                left join ventas v "+
        "                on t.idlote = v.idlote and "+
        "                   estadoActual = 'A' "+
        " group by d.iddesarrollo, " +
        "          t.staventa, " +
        "          case when StaVenta = 'A' then case when idtipointercambio = 'N' then 'NI' when idtipointercambio = 'E' then 'E' else 'I' end when idtipointercambio = 'N' or idtipointercambio is null then 'NI' else 'I' end " +
        " order by 1, " +
        "          2, " +
        "          3 ";
    Statement  stmt_csreport = ConnRs.createStatement();
    ResultSet  rs_csreport   = stmt_csreport.executeQuery( sql_csreport );

    String  desaTmp    = "";
    String  desaAntTmp = "";
    char statusTmp = ' ';
    char intercTmp = ' ';
    int  totalTmp  = 0;
    int  i         = 0;

    while( rs_csreport.next() ){
      desaTmp   = rs_csreport.getString("iddesarrollo");
      statusTmp = rs_csreport.getString("staventa").charAt(0);
      intercTmp = rs_csreport.getString("intercambio").charAt(0);
      totalTmp  = rs_csreport.getInt("total");

      if( !desaTmp.equals(desaAntTmp) && i > 0 ){
        status[9] = new Integer ( ( (Integer)status[1]).intValue()+((Integer)status[2]).intValue()+((Integer)status[3]).intValue()+((Integer)status[4]).intValue()+((Integer)status[5]).intValue()+((Integer)status[6]).intValue()+((Integer)status[7]).intValue()+((Integer)status[8]).intValue()+( (Integer)status[10]).intValue() );
        desarrollos.add(status);
        status = new Object[11];
        status[0] = new Integer( 0 );
        status[1] = new Integer( 0 );
        status[2] = new Integer( 0 );
        status[3] = new Integer( 0 );
        status[4] = new Integer( 0 );
        status[5] = new Integer( 0 );
        status[6] = new Integer( 0 );
        status[7] = new Integer( 0 );
        status[8] = new Integer( 0 );
        status[9] = new Integer( 0 );
        status[10] = new Integer( 0 );
      }

      status[0] = desaTmp;
      if( statusTmp == Terrain.AVAILABLE ){
        status[(intercTmp == Terrain.EXCHANGE?2:1)] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.APART ){
        status[(intercTmp == Terrain.EXCHANGE?4:(intercTmp == Terrain.ESPECIAL ? 10 : 3 ) )] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.SOLD ){
        status[(intercTmp == Terrain.EXCHANGE?6:5)] = new Integer( totalTmp );
      }
      if( statusTmp == Terrain.CONTRACT ){
        status[(intercTmp == Terrain.EXCHANGE?8:7)] = new Integer( totalTmp );
      }

      i++;
      desaAntTmp = desaTmp;
    }
    status[9] = new Integer ( ( (Integer)status[1]).intValue()+((Integer)status[2]).intValue()+((Integer)status[3]).intValue()+((Integer)status[4]).intValue()+((Integer)status[5]).intValue()+((Integer)status[6]).intValue()+((Integer)status[7]).intValue()+((Integer)status[8]).intValue()+( (Integer)status[10]).intValue() );
    desarrollos.add(status);

    ConnRs.close();
    return desarrollos.toArray();
  }
  public void saveData(HttpSession session) throws Exception {
    /**@todo Implement this kines.SerializableDB method*/
    throw new java.lang.UnsupportedOperationException("Method saveData() not yet implemented.");
  }
  public boolean deleteData() throws Exception {
    /**@todo Implement this kines.SerializableDB method*/
    throw new java.lang.UnsupportedOperationException("Method deleteData() not yet implemented.");
  }

  public static Object[] getConcentradoPlan(String desarrollo)throws SQLException, Exception{
	  return getConcentradoPlan(desarrollo, null);
  }
  public static Object[] getConcentradoPlan(String desarrollo, String dueno)throws SQLException, Exception{
	    ArrayList all = new ArrayList();
	    Class.forName(driver).newInstance();
	    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
	    String query = "select ordena,"+
	                        " planes.noplan,"+
	                        " case "+
	                        "       when planes.plazo = 0 and planes.noplan <> -1 then 'Contado' "+
	                        "       else nombrePlan "+
	                        " end nombrePlan,"+
	                        " planes.iddesarrollo,"+
	                        " planes.plazo,"+
	                        " IfNULL(num_lotes, 0 ) AS num_lotes,"+
	                        " IfNULL(precio_promedio, 0)AS precio_promedio,"+
	                        " IfNULL(venta_total, 0)AS venta_total,"+
	                        " ( 100.0/(SELECT COUNT(*) "+
	                                   " FROM lotes l"+
	                                   " LEFT JOIN ventas v "+
	                                       " ON l.IDLOTE = v.IDLOTE"+
	                                   " LEFT JOIN planesfinanciamiento pf"+
	                                       " ON v.IDPLAN = pf.IDPLAN "+
	                                       //" AND l.IDDESARROLLO = pf.IDDESARROLLO"+
	                                   " WHERE l.IDDESARROLLO = '"+desarrollo+"' "+((dueno!=null&&!dueno.equals(""))?" and iddueno = '"+dueno+"' ":"")+" and"+
	                                    " v.ESTADOACTUAL = 'A' AND"+
	                                    " l.StaVenta='C' and pf.NoPlan is not null ))*tablatmp.num_lotes AS porcentaje"+
	                        " from"+
	                        " ("+
	                        " select NoPlan as ordena,	NoPlan, plazo, "+
	                        " NoPlan as nombrePlan,"+
	                        " iddesarrollo"+
	                        " from	planesfinanciamiento "+
	                        " union"+
	                        " select 100,	-1, 0 ,"+
	                        "         'Especial',"+
	                        "         '"+desarrollo+"'"+
	                        " ) as planes"+
	                        "                 left join ("+
	                        "                         select  iddesarrollo,"+
	                        "                                 NoPlan,"+
	                        "                                 ( select	plazo from	planesfinanciamiento where	planesfinanciamiento.iddesarrollo = tablatmp.iddesarrollo and planesfinanciamiento.NoPlan = tablatmp.NoPlan ) as Plazo, "+
	                        "                                 Num_Lotes,"+
	                        "                                 Precio_Promedio,"+
	                        "                                 Venta_Total"+
	                        "                         from	("+
	                        "                                 SELECT   l.iddesarrollo,"+
	                        "                                          case"+
	                        "                                                 when v.CreditoEspecial = 'S' or v.EngDiferido = 'S' then '-1'"+
	                        "                                                 else '' + pf.NoPlan"+
	                        "                                          end NoPlan, "+
	                        "                                          count(*) as num_lotes,"+
	                        "                                          sum( v.preciototal * "+
	                        "                                               ( case "+
	                        "                                                    when v.idmoneda = 1 then 1 "+
	                        "                                                    else tc.tipodecambio "+
	                        "                                                 end ) " +
	                        "                                          ) as venta_total,"+
	                        "                                          sum( "+
	                        "                                               ( case "+
	                        "                                                    when v.idmoneda = 1 then (v.PrecioTotal / l.Superficie) "+
	                        "                                                    else (v.PrecioTotal / l.Superficie) * tc.tipodecambio "+
	                        "                                                 end ) " +
	                        "                                           ) / count(*) as Precio_Promedio"+
	                        "                                 FROM	   lotes l "+
	                        "                                                 LEFT JOIN ventas v"+
	                        "                                                 ON l.IDLOTE = v.IDLOTE"+
	                        "                                                 LEFT JOIN planesfinanciamiento pf"+
	                        "                                                 ON v.IDPLAN = pf.IDPLAN "+
	                        "                                                 LEFT JOIN tipodecambio tc "+
	                        "                                                 ON tc.FECHA = v.FECHAFIRMA "+
	                        "                                 WHERE	   l.IDDESARROLLO = '"+desarrollo+"' "+((dueno!=null&&!dueno.equals(""))?" and iddueno = '"+dueno+"' ":"")+" and"+
	                        "                                          v.ESTADOACTUAL = 'A' AND"+
	                        "                                          l.StaVenta='C' AND "+
	                        "                                          pf.NoPlan is not null "+
	                        "                                 GROUP BY case"+
	                        "                                                 when v.CreditoEspecial = 'S' or v.EngDiferido = 'S' then '-1'"+
	                        "                                                 else '' + pf.NoPlan"+
	                        "                                          end,"+
	                        "                                          l.iddesarrollo"+
	                        "                         ) as tablatmp"+
	                        "                 ) as tablatmp"+
	                        "                 on tablatmp.NoPlan = planes.NoPlan"+
	                        " WHERE	planes.iddesarrollo = '"+desarrollo+"'"+
	                        " ORDER BY 1";
	                    Statement select = ConnRs.createStatement();
	                    ResultSet rows = select.executeQuery(query);

	  String nombrePlan = "";
	  int plazo = 0;
	  int num_lotes = 0;
	  double precio_promedio = 0;
	  double venta_total = 0;
	  double porcentaje = 0;
	  int noplan = 0;

	                    while(rows.next()){
	                      nombrePlan = rows.getString("nombrePlan");
	                      plazo = rows.getInt("plazo");
	                      num_lotes = rows.getInt("num_lotes");
	                      precio_promedio = rows.getDouble("precio_promedio");
	                      venta_total = rows.getDouble("venta_total");
	                      porcentaje = rows.getDouble("porcentaje");
	                      noplan = rows.getInt("NoPlan");
	                      Object [] row = { nombrePlan, new Integer(plazo),
	                          new Integer(num_lotes), new Double(precio_promedio),
	                          new Double(venta_total), new Double(porcentaje),
	                          new Integer(noplan)
	                      };
	                      all.add(row);
	                    }
	                    select.close();
	                    ConnRs.close();
	    return all.toArray();
	  }

  public static Object [] getConcentradoPrecio(String desarrollo)throws SQLException , Exception{
	  return getConcentradoPrecio(desarrollo, null);
  }
  public static Object [] getConcentradoPrecio(String desarrollo, String dueno)throws SQLException , Exception{
    ArrayList all = new ArrayList();
    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String query =
        " select round( v.PrecioTotal / lotes.Superficie, 2 ) as PrecioXMt,"+
        "        count(*)AS total,"+
        "        sum( superficie ) as superficie,"+
        "        sum( v.preciototal ) as venta_total,"+
        "        count(*) * ("+
        "                 select 100.0/count(*)"+
        "                 from	 lotes"+
        "                 where	 iddesarrollo = '"+desarrollo+"' "+((dueno!=null&&!dueno.equals(""))?" and iddueno = '"+dueno+"' ":"")+" and"+
        "                        staventa = 'C' and"+
        "                        (v.PrecioTotal / lotes.Superficie)!=0.0"+
        "        ) as porcentaje"+
        " from	 lotes"+
        "              inner join ventas v "+
        "              on lotes.idlote = v.idlote and "+
        "                 v.estadoactual ='A' "+
        " where	 iddesarrollo = '"+desarrollo+"' "+((dueno!=null&&!dueno.equals(""))?" and iddueno = '"+dueno+"' ":"")+" and"+
        "        staventa = 'C' and"+
        "        (v.PrecioTotal / lotes.Superficie)!=0.0"+
        " group by round( v.PrecioTotal / lotes.Superficie, 2 ) "+
        " order by 1";
    //System.out.println("query->"+query);
    Statement select = ConnRs.createStatement();
    ResultSet rows = select.executeQuery(query);

    double precioXMt = 0;
    int total = 0;
    double superficie = 0;
    double venta_total = 0;
    double porcentaje = 0;

    while(rows.next()){
      precioXMt = rows.getDouble("precioXMt");
      total = rows.getInt("total");
      superficie = rows.getDouble("superficie");
      venta_total = rows.getDouble("venta_total");
      porcentaje = rows.getDouble("porcentaje");
      Object [] row = {new Double(precioXMt), new Integer(total), new Double(superficie),
          new Double(venta_total), new Double(porcentaje)};
      all.add(row);
    }

    select.close();
    ConnRs.close();
    return all.toArray();
  }

  public static Object[] getEvolucionVentasDetalle(int int_mes, int int_anio,int int_mesS, int int_anioS, String str_tipo, String str_tipoVenta, String str_desarrollo)throws SQLException, Exception{
    ArrayList arr_rows = new ArrayList();
    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);
    String str_query = "EXEC dbo.reporteEvolucionMensualVentas "+int_mes+" ,"+int_anio+", "+int_mesS+", "+int_anioS+", '"+str_tipo+"', '"+str_tipoVenta+"', '"+str_desarrollo+"'";
    ResultSet rs_select = ConnRs.createStatement().executeQuery(str_query);
    Calendar cal_fecha;
    Date dte_tmp;
    String str_fechaFirmaApartado;
    String str_fechaEntrega;
    String str_cveLote;
    int int_total = 0;
    String str_precio;
    double dbl_precio;
    double dbl_precioTotal = 0;
    String str_superficie;
    double dbl_superficie;
    double dbl_superficieTotal = 0;
    String str_precioTotal;
    double dbl_precioTotalT;
    double dbl_precioTotalTTotal = 0;
    String str_enganche;
    double dbl_enganche;
    double dbl_engancheTotal = 0;
    String str_plazo;
    String str_vendedor;
    String str_vendedorNombre;
    String str_cliente;
    String str_clienteCorto;
    while( rs_select.next() ){
      int_total++;
      dte_tmp = rs_select.getDate("FECHAFIRMAAPARTADO");
      if( dte_tmp != null ){
        cal_fecha = Calendar.getInstance();
        cal_fecha.setTime(dte_tmp);
        str_fechaFirmaApartado = sdf.format(cal_fecha.getTime());
      }else{
        str_fechaFirmaApartado = "";
      }
      dte_tmp = rs_select.getDate("FECHAENTREGA");
      if( dte_tmp != null ){
        cal_fecha = Calendar.getInstance();
        cal_fecha.setTime(dte_tmp);
        str_fechaEntrega = sdf.format(cal_fecha.getTime());
      }else{
        str_fechaEntrega = "";
      }
      str_cveLote = rs_select.getString("CLAVELOTE");
      dbl_precio = rs_select.getDouble("PRECIOXMT2");
      dbl_precioTotal += dbl_precio;
      str_precio = nf.format(dbl_precio);
      dbl_superficie = rs_select.getDouble("SUPERFICIE");
      dbl_superficieTotal += dbl_superficie;
      str_superficie = nf.format(dbl_superficie);
      dbl_precioTotalT = rs_select.getDouble("TOTAL");
      dbl_precioTotalTTotal += dbl_precioTotalT;
      str_precioTotal = nf.format(dbl_precioTotalT);
      dbl_enganche = rs_select.getDouble("ENGANCHE");
      dbl_engancheTotal += dbl_enganche;
      str_enganche = nf.format(dbl_enganche);
      str_plazo = rs_select.getString("PLAZO");
      str_vendedor = rs_select.getString("VENDEDOR");
      str_vendedorNombre = rs_select.getString("VENDEDORNOMBRE");
      str_cliente = rs_select.getString("CLIENTE");
      str_clienteCorto = str_cliente.length() > 20 ? str_cliente.substring(0,17)+"..." : "";
      Object [] obj_campos = {
          str_fechaFirmaApartado,
          str_fechaEntrega,
          str_cveLote,
          str_precio,
          str_superficie,
          str_precioTotal,
          str_enganche,
          str_plazo,
          str_vendedor,
          str_clienteCorto,
          str_vendedorNombre,
          str_cliente
      };
      arr_rows.add(obj_campos);
    }
    Object [] obj_campos = {
        String.valueOf(int_total),
        nf.format(dbl_precioTotal),
        nf.format(dbl_superficieTotal),
        nf.format(dbl_precioTotalTTotal),
        nf.format(dbl_engancheTotal)
    };
    arr_rows.add(obj_campos);
    ConnRs.close();
    return arr_rows.toArray();
  }

  public static Object[] getEvolucionVentas(int int_mes, int int_anio,int int_mesS, int int_anioS)throws SQLException, Exception{
    ArrayList arr_rows = new ArrayList();

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);

    String str_query = "EXEC dbo.reporteObjetivosVentas "+int_mes+" ,"+int_anio+", "+int_mesS+", "+int_anioS;

    ResultSet rs_select = ConnRs.createStatement().executeQuery(str_query);

    String str_desarrollo = "";
    int int_unidades = 0;
    double dbl_monto = 0;
    int int_apartados = 0;
    double dbl_apartados = 0;
    int int_enganches = 0;
    double dbl_enganches = 0;
    int int_contratados = 0;
    double dbl_contratados = 0;
    int int_apartados_i = 0;
    double dbl_apartados_i = 0;
    int int_enganches_i = 0;
    double dbl_enganches_i = 0;
    int int_contratados_i = 0;
    double dbl_contratados_i = 0;


    while(rs_select.next()){
      str_desarrollo = rs_select .getString("desarrollo");
      int_unidades = rs_select.getInt("unidades");
      dbl_monto = rs_select.getDouble("monto");
      int_apartados = rs_select.getInt("apartados");
      dbl_apartados = rs_select.getDouble("monto_apartado");
      int_enganches = rs_select.getInt("enganches");
      dbl_enganches = rs_select.getDouble("monto_enganches");
      int_contratados = rs_select.getInt("contratados");
      dbl_contratados = rs_select.getDouble("monto_contratados");
      int_apartados_i = rs_select.getInt("apartados_intercambio");
      dbl_apartados_i = rs_select.getDouble("monto_apartado_intercambio");
      int_enganches_i = rs_select.getInt("enganches_intercambio");
      dbl_enganches_i = rs_select.getDouble("monto_enganches_intercambio");
      int_contratados_i = rs_select.getInt("contratados_intercambio");
      dbl_contratados_i = rs_select.getDouble("monto_contratados_intercambio");


      Object [] obj_row = {str_desarrollo, new Integer(int_unidades),
          new Double(Math.round( dbl_monto/1000 ) ),
          new Integer(int_apartados), new Double(Math.round( dbl_apartados/1000 )), new Integer(int_enganches),
          new Double(Math.round( dbl_enganches/1000 )),  new Integer(int_contratados), new Double(Math.round( dbl_contratados/1000 ) ),
          new Integer(int_apartados_i), new Double(Math.round( dbl_apartados_i/1000 )), new Integer(int_enganches_i),
          new Double(Math.round( dbl_enganches_i/1000 )), new Integer(int_contratados_i), new Double(Math.round( dbl_contratados_i/1000 ) ) };
      arr_rows.add(obj_row);
    }
    ConnRs.close();

    return arr_rows.toArray();
  }

  public static Object [] getListaCaptura(int mes, int anio)throws SQLException, Exception{
    ArrayList arr_rows= new ArrayList();

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);

    String str_select = "SELECT iddesarrollo,"+
                              " IfNULL(o.unidades, 0)AS unidades,"+
                              " d.lote_tipo,"+
                              " precioxmtdesarrollo,"+
                              " IfNULL(o.unidades, 0) * d.lote_tipo * precioxmtdesarrollo AS total"+
                        " FROM desarrollos d"+
                              " LEFT JOIN c_objetivo_ventas o ON o.desarrollo = d.iddesarrollo"+
                                        " AND o.mes = "+mes+" AND anio = "+anio;

    ResultSet rs_select = ConnRs.createStatement().executeQuery(str_select);

    String str_desarrollo = "";
    int int_unidades = 0;
    int int_lote_tipo = 0;
    double dbl_precioMt = 0;
    double dbl_total = 0;

    while(rs_select.next()){
      str_desarrollo = rs_select.getString("iddesarrollo");
      int_unidades = rs_select.getInt("unidades");
      int_lote_tipo = rs_select.getInt("lote_tipo");
      dbl_precioMt = rs_select.getDouble("precioxmtdesarrollo");
      dbl_total = rs_select.getDouble("total");

      Object [] obj_row = {str_desarrollo, new Integer(int_unidades), new Integer(int_lote_tipo),
          new Double(dbl_precioMt), new Double(dbl_total)};
      arr_rows.add(obj_row);
    }
    ConnRs.close();

    return arr_rows.toArray();
  }

  /**
   * Actualiza objetivos de venta para desarrollos
   * @param objetivos String[] datos a guardar
   *               0 - desarrollo
   *               1 - mes
   *               2 - anio
   *               3 - unidades
   * @throws SQLException
   * @throws Exception
   */

  public static void guardaObjetivos(Object [] objetivos)throws SQLException, Exception{

    Class.forName(driver).newInstance();
    Connection ConnRs        = DriverManager.getConnection(strconn,username,password);

    String str_select = "";

    for(int i=0; i<objetivos.length; i++){
      str_select = "EXEC guardaObjetivo '"+objetivos[0]+"', "+objetivos[1]+", "+objetivos[2]+", "+objetivos[3];

      ConnRs.createStatement().executeUpdate(str_select);

    }
  }


  public static String comboSemanas(String str_semana)throws SQLException, Exception{
    String str_return = "";

     Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username, password);
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

    String str_select = "SELECT fechaini,"+
                              " fechafin "+
                        " FROM semanas";

    ResultSet rs_select = ConnRs.createStatement().executeQuery(str_select);

    String str_inicio = "";
    String str_fin;
    
    java.sql.Date dte_inicio;
    java.sql.Date dte_fin;

    while(rs_select.next()){
    	
    	dte_inicio = rs_select.getDate("fechaini");
        dte_fin = rs_select.getDate("fechafin");
        
      str_inicio = sdf.format(dte_inicio);
      str_fin = sdf.format(dte_fin);
      
      str_return += "<option value=\""+str_inicio+"\" "+(str_inicio.equals(str_semana)? " selected": "")+">"+str_inicio+" - "+str_fin+"</option>";
    }

    ConnRs.close();

    return str_return;
  }

  public static String [] primerasSemanas(String str_semana)throws SQLException, Exception{
    String str_return [] = {"","","",""};
    
    SimpleDateFormat sdfMx = new SimpleDateFormat( "dd/MM/yyyy" );
    SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );

     Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username, password);

    String str_select = "SELECT CONCAT( DATE_FORMAT( fechaini, '%d/%c/%Y' ),' - ',DATE_FORMAT( fechafin, '%d/%c/%Y' ) ) AS fecha"+
                        " FROM semanas"+
                        " WHERE FECHAINI >= '"+sdf.format(sdfMx.parse(str_semana))+"' limit 4;";
    
    ResultSet rs_select = ConnRs.createStatement().executeQuery(str_select);


    int i = 0;
    while(rs_select.next()){
      str_return[i] = rs_select.getString("fecha");
      i++;
    }


    ConnRs.close();

    return str_return;
  }

  public static Object[] proyeccionCobranza(Calendar cal_inicio, Calendar cal_fin)throws Exception{
	  return proyeccionCobranza(cal_inicio, cal_fin, null);
  }
  
  public static Object[] proyeccionCobranza(Calendar cal_inicio, Calendar cal_fin, String dueno)throws Exception{
    ArrayList arrL_retVal = new ArrayList();
    ArrayList arrL_semanas = new ArrayList();

    Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username, password);

    SimpleDateFormat sdf_n = new SimpleDateFormat("yyyy/MM/dd");
    String str_proy = "call sp_reporteProyeccionCobranza ('"+sdf_n.format(cal_inicio.getTime())+"', '"+sdf_n.format(cal_fin.getTime())+"', '"+dueno+"') ";
    
    Statement stmt_proy = ConnRs.createStatement();
    ResultSet rs_proy = stmt_proy.executeQuery(str_proy);

    String str_desarrollo = "";
    String str_desarrolloAnt = "";
    double dbl_porVencer;
    double dbl_vencido;
    int int_i = 0;
    while( rs_proy.next() ){
      str_desarrollo = rs_proy.getString("idDesarrollo");
      dbl_vencido = rs_proy.getDouble("vencido");
      dbl_porVencer = rs_proy.getDouble("porvencer");
      if(int_i>0 && !str_desarrolloAnt.equals(str_desarrollo)){
        Object [] objs_campos = {str_desarrolloAnt,separarSemanasPeriodos(arrL_semanas)};
        arrL_retVal.add(objs_campos);
        arrL_semanas = new ArrayList();
      }else{
        Object [] objs_montos = {new Double(dbl_vencido), new Double(dbl_porVencer)};
        arrL_semanas.add(objs_montos);
      }
      str_desarrolloAnt = str_desarrollo;
      int_i++;
    }
    if( int_i++ > 0 ){
    	Object [] objs_campos = {str_desarrolloAnt,separarSemanasPeriodos(arrL_semanas)};
        arrL_retVal.add(objs_campos);
    }
    stmt_proy.close();
    rs_proy.close();

    ConnRs.close();

    return arrL_retVal.toArray();
  }
  private static ArrayList separarSemanasPeriodos(ArrayList arrL_semanas){
    ArrayList arrL_retVal = new ArrayList();
    double dbl_vencido;
    double dbl_porVencer;

    double dbl_vencidoTotal = 0;
    double dbl_porVencerTotal = 0;

    for( int int_i = 0; int_i < arrL_semanas.size(); int_i++ ){
      if( int_i < 4 )arrL_retVal.add(arrL_semanas.get(int_i));
      else{
        dbl_vencido = ((Double)((Object [])arrL_semanas.get(int_i))[0] ).doubleValue();
        dbl_porVencer = ((Double)((Object [])arrL_semanas.get(int_i))[1] ).doubleValue();

        if( ( int_i > 4 ) && ( int_i%4 == 0 ) ){
          Object [] objs_montos = {new Double(dbl_vencidoTotal), new Double(dbl_porVencerTotal)};
          arrL_retVal.add(objs_montos);

          dbl_vencidoTotal = 0;
          dbl_porVencerTotal = 0;
        }

        dbl_vencidoTotal += dbl_vencido;
        dbl_porVencerTotal += dbl_porVencer;
      }
    }
    Object [] objs_montos = {new Double(dbl_vencidoTotal), new Double(dbl_porVencerTotal)};
    arrL_retVal.add(objs_montos);

    return arrL_retVal;
  }
}
